package org.jiaxin.nutrient.utils;

import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @Author: jiaxin
 * @Description : TODO 统一使用JDBC的连接外部库的工具类
 * @date: 2022/9/19 17:38
 **/

public class JDBCUtils {

    /**
     * @param connection
     * @return Map
     * @throws SQLException TODO 获取 SQLServer的元数据信息 并将元数据信息封装为Map类型  key为Col的名称 value为类型 meta
     */
    public static Map<String, String> getSqlServerMateDataWithMap(Connection connection, String tableName) throws Exception {
        if (connection != null && tableName != null) {
            Map<String, String> linkedHashMap = new LinkedHashMap<>();
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tableInfo = metaData.getColumns(null, "%", tableName, "%");
            while (tableInfo.next()) {
                String type_name = tableInfo.getString("TYPE_NAME");
                //消除 主键 identity 关键字的影响
                if (type_name.contains("identity")){
                    type_name=type_name.replace(" identity","");
                }
                linkedHashMap.put(tableInfo.getString("COLUMN_NAME"), type_name);
            }
            return linkedHashMap;
        }

        throw new Exception("输入的参数Connection 或者 TableName 有问题 !!!!!!!!!请重新输入  " + "\n connection :" + connection + "\n TableName : " + tableName);
    }

    /**
     * @param connection
     * @param tableName
     * @return Set
     * @throws SQLException TODO 只是返回 列的名称 且只用set集合
     */
    public static Set<String> getSqlServerMateDataWithSet(Connection connection, String tableName) throws Exception {
        return getSqlServerMateDataWithMap(connection, tableName).keySet();
    }

    /**
     *
     * @param userName
     * @param passWord
     * @param url
     * @param tableName
     * @return
     * @throws Exception
     * TODO  获取 SQLServer的元数据信息 并将元数据信息封装为Map类型  key为Col的名称 value为类型
     */
    public static Map<String, String> getSqlServerMateDataWithMap(String userName, String passWord, String url, String tableName) throws Exception {
        if (userName != null && passWord != null && url != null && tableName != null) {
            Connection connection = JdbcConnection.getSQLServerConnection(userName, passWord, url);
            return  getSqlServerMateDataWithMap(connection, tableName);
        }
        throw new Exception("输入的参数 有问题 !!!!!!!!!请重新输入  " + "\n userName :" + userName + "\n passWord : " + passWord
                + "\n url : " + url + "\n tableName : " + tableName);
    }

    /**
     *
     * @param userName
     * @param passWord
     * @param url
     * @param tableName
     * @return
     * @throws Exception
     * TODO 只是返回 列的名称 且只用set集合
     */
    public static Set<String> getSqlServerMateDataWithSet(String userName, String passWord, String url, String tableName) throws Exception {
        return getSqlServerMateDataWithMap(userName, passWord,url,tableName).keySet();
    }


    public static List<String> getDBAllTables(Connection connection) throws SQLException {

        return getDBAllTables(connection,"");
    }

    public static List<String> getDBAllTables(Connection connection,String regex) throws SQLException {
        List<String> list = new LinkedList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        String[] types = {"TABLE"};
        ResultSet tables = metaData.getTables(null, null, "%", types);
        while (tables.next()){
            String table_name = tables.getString("TABLE_NAME");
            if (StringUtils.isBlank(regex)){
                list.add(table_name);
            }else {
                if (table_name.matches(regex)){
                    list.add(table_name);
                }
            }

        }
       return list;
    }





}
